package com.apes.fico.finance.service;

import com.apes.fico.finance.respository.InitAuxRepository;
import com.apes.fico.finance.respository.InitBalanceRepository;
import com.apes.framework.api.DomainService;
import com.apes.framework.plugin.esb.api.SimpleRequest;
import com.apes.framework.util.Arith;
import com.apes.framework.util.MapUtil;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * @ClassName FinancialDetailsService
 * @Description TODO
 * @Author pengyb
 * @Date 2019/3/8 12:00
 * @Version 1.0
 **/
@Service("financialDetailsService")
public class FinancialDetailsService extends DomainService {

    @Autowired
    private EntityManager entityManager;

    @Autowired
    private InitBalanceRepository initBalanceRepository;

    @Autowired
    private InitAuxRepository initAuxRepository;

    /**
     * 查询财务凭证明细帐
     *
     * @param request
     * @return
     */
    public List<Map<String, String>> findAll(SimpleRequest request) {
        //取值
        String company = request.get("company");
        int month = request.get("month");
        int tillMonth = request.get("tillMonth");
        int year = request.get("year");
        int tillYear = request.get("tillYear");
        String accCode = request.get("accCode"); ///科目id
        Boolean checkAux = request.get("checkOne");
        String auxName = request.get("auxName");
//        String aux = checkAux ? "B.AUX" : "null";
//        String auxTemp = checkAux ? !"".equals(auxName) ? "A.NAME" : "NULL" : "NULL";
        String accCodeCon = Objects.nonNull(accCode) ? " AND C.CODE = '" + accCode + "'" : " AND 1 = 1 ";
        String auxNameConC = !Objects.equals(auxName,"") ? " AND T.AUX LIKE '%" + auxName + "%'" : " AND 1= 1 ";
//        String auxs = !Objects.equals(auxName,"") ? "AND G.NAME LIKE T.AUX" : "AND 1 = 1";
        List<Map<String, String>> voucherItemsMaps;
        //拼接SQL
        StringBuffer sSql = new StringBuffer();
        if(checkAux){
            sSql.append("SELECT SUBSTR(T.YEAR_MONTH,0,4) YEAR, T.POST_DATE, C.CODE AS ACC_OPERATING_CHART_ID, C.NAME, T.VOUCHER_NO ID, T.TYPE, T.AUX, T.SUMMARY,    " +
                    "       CASE WHEN SUMMARY='本期合计' THEN   " +
                    "           SUM(T.DEBIT) OVER (PARTITION BY C.CODE, T.AUX_KEY, T.YEAR_MONTH)   " +
                    "         WHEN SUMMARY='本年累计' THEN   " +
                    "       NVL((SELECT SUM(DEBIT+UN_DEBIT)  " +
                    "                FROM GL_CHART_AUX G " +
                    "                WHERE G.COMPANY_ID = T.COMPANY_ID " +
                    "                AND G.ACC_OPERATING_CHART_ID = T.ACC_OPERATING_CHART_ID " +
                    "                AND G.YEAR = T.YEAR " +
                    "                AND G.KEY = T.AUX_KEY " +
                    "               AND G.PERIOD > '202202' " +
                    "                AND G.PERIOD <= T.YEAR_MONTH), " +
                    "        (SELECT SUM(DEBIT+UN_DEBIT) " +
                    "        FROM GL_CHART G " +
                    "        WHERE G.COMPANY_ID = T.COMPANY_ID " +
                    "        AND G.ACC_OPERATING_CHART_ID = T.ACC_OPERATING_CHART_ID " +
                    "        AND G.YEAR = T.YEAR " +
                    "       AND G.PERIOD > '202202' " +
                    "        AND G.PERIOD <= T.YEAR_MONTH)) " +
                    "         ELSE T.DEBIT END DEBIT,    " +
                    "       CASE WHEN SUMMARY='本期合计' THEN   " +
                    "           SUM(T.CREDIT) OVER (PARTITION BY C.CODE, T.AUX_KEY, T.YEAR_MONTH)   " +
                    "         WHEN SUMMARY='本年累计' THEN   " +
                    "        NVL((SELECT SUM(CREDIT+UN_CREDIT) " +
                    "                FROM GL_CHART_AUX G " +
                    "                WHERE G.COMPANY_ID = T.COMPANY_ID " +
                    "                AND G.ACC_OPERATING_CHART_ID = T.ACC_OPERATING_CHART_ID " +
                    "                AND G.YEAR = T.YEAR " +
                    "                AND G.KEY = T.AUX_KEY " +
                    "               AND G.PERIOD > '202202' " +
                    "                AND G.PERIOD <= T.YEAR_MONTH), " +
                    "        (SELECT SUM(CREDIT+UN_CREDIT) " +
                    "        FROM GL_CHART G " +
                    "        WHERE G.COMPANY_ID = T.COMPANY_ID " +
                    "        AND G.ACC_OPERATING_CHART_ID = T.ACC_OPERATING_CHART_ID " +
                    "        AND G.YEAR = T.YEAR " +
                    "       AND G.PERIOD > '202202' " +
                    "        AND G.PERIOD <= T.YEAR_MONTH)) " +
                    "         ELSE T.CREDIT END CREDIT,   " +
                    "       SUM(T.YE+T.DEBIT-T.CREDIT) OVER (PARTITION BY C.CODE, T.AUX_KEY, T.YEAR_MONTH ORDER BY T.POST_DATE, TYPE, T.ITEM_ID) BALANCE，   " +
                    "       ABS(SUM(T.YE+T.DEBIT-T.CREDIT) OVER (PARTITION BY C.CODE, T.AUX_KEY, T.YEAR_MONTH ORDER BY T.POST_DATE, TYPE, T.ITEM_ID)) BALANCE_FORMAT   " +
                    "  FROM (SELECT A.YEAR, A.COMPANY_ID, A.POST_DATE, A.VOUCHER_NO, B.ID ITEM_ID, A.YEAR_MONTH, B.ACC_OPERATING_CHART_ID, B.SUMMARY,   " +
                    "                B.DEBIT, B.CREDIT, 0 YE, B.AUX , 0 OPENING_BALANCE, 2 - HANDWORK_FLAG TYPE, B.COLLECT_AUX AUX_KEY   " +
                    "           FROM GL_VOUCHER A, GL_VOUCHER_ITEM B   " +
                    "          WHERE A.ID = B.VOUCHER_ID   " +
                    "         UNION ALL   " +
                    "         SELECT A.YEAR, A.COMPANY_ID, TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD') POST_DATE, NULL VOUCHER_NO, '0' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '期初余额' SUMMARY, 0 DEBIT, 0 CREDIT,   " +
                    "                OPENING_BALANCE YE, NULL AUX,OPENING_BALANCE, 0 TYPE,NULL AUX_KEY   " +
                    "           FROM GL_CHART A   " +
                    "         UNION ALL   " +
                    "         SELECT A.YEAR, A.COMPANY_ID, TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD') POST_DATE, NULL VOUCHER_NO, '0' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '期初余额' SUMMARY, 0 DEBIT, 0 CREDIT,   " +
                    "                SUM(OPENING_BALANCE) YE, A.NAME AUX, 0 OPENING_BALANCE, 0 TYPE,A.KEY AUX_KEY   " +
                    "           FROM GL_CHART_AUX A   " +
                    "          GROUP BY A.YEAR, A.COMPANY_ID, REPLACE(TO_CHAR(A.PERIOD / 100), '.', '-') || '-01', A.PERIOD, A.ACC_OPERATING_CHART_ID,  A.NAME,A.KEY" +
                    "          UNION ALL   " +
                    "          SELECT A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')) POST_DATE, NULL VOUCHER_NO, 'Z1' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '本期合计' SUMMARY, 0 DEBIT, 0 CREDIT, 0 YE,NULL AUX, 0 OPENING_BALANCE, 3 TYPE, NULL AUX_KEY   " +
                    "           FROM GL_CHART A   " +
                    "         UNION ALL   " +
                    "         SELECT A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')) POST_DATE, NULL VOUCHER_NO, 'Z1' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '本期合计' SUMMARY, 0 DEBIT, 0 CREDIT, 0 YE, A.NAME AUX, 0 OPENING_BALANCE, 3 TYPE,A.KEY AUX_KEY   " +
                    "           FROM GL_CHART_AUX A   " +
                    "          GROUP BY A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')), A.PERIOD, A.ACC_OPERATING_CHART_ID,   A.NAME,A.KEY" +
                    "          UNION ALL   " +
                    "          SELECT A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')) POST_DATE, NULL VOUCHER_NO, 'Z2' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '本年累计' SUMMARY, 0 DEBIT, 0 CREDIT, 0 YE,NULL AUX, 0 OPENING_BALANCE, 4 TYPE,NULL AUX_KEY   " +
                    "           FROM GL_CHART A   " +
                    "         UNION ALL   " +
                    "         SELECT A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')) POST_DATE, NULL VOUCHER_NO, 'Z2' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '本年累计' SUMMARY, 0 DEBIT, 0 CREDIT, 0 YE,  A.NAME AUX, 0 OPENING_BALANCE, 4 TYPE,A.KEY AUX_KEY   " +
                    "           FROM GL_CHART_AUX A   " +
                    "          GROUP BY A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')), A.PERIOD, A.ACC_OPERATING_CHART_ID,  A.NAME,A.KEY "+
                    "          ) T, MAS_ACC_OPERATING_CHART C" +
                    " WHERE T.ACC_OPERATING_CHART_ID = C.ID   " +
                    "   AND T.COMPANY_ID ='" + company + "' AND (T.YEAR BETWEEN '" + year + "' AND '" + tillYear + "') " +
                    "   AND (T.YEAR_MONTH BETWEEN '" + month + "' AND '" + tillMonth + "') " + accCodeCon + auxNameConC +
                    " ORDER BY C.CODE, T.AUX_KEY, T.YEAR_MONTH, T.POST_DATE, T.TYPE, T.ITEM_ID");
        } else {
            sSql.append("SELECT SUBSTR(T.YEAR_MONTH,0,4) YEAR, T.POST_DATE, C.CODE AS ACC_OPERATING_CHART_ID, C.NAME, T.VOUCHER_NO ID, T.TYPE, T.SUMMARY,    " +
                    "       CASE WHEN SUMMARY='本期合计' THEN   " +
                    "           SUM(T.DEBIT) OVER (PARTITION BY C.CODE, T.YEAR_MONTH)   " +
                    "         WHEN SUMMARY='本年累计' THEN   " +
                    "           NVL((SELECT SUM(DEBIT+UN_DEBIT) FROM GL_CHART G WHERE G.COMPANY_ID=T.COMPANY_ID AND G.YEAR=T.YEAR " +
                    "                   AND G.ACC_OPERATING_CHART_ID=T.ACC_OPERATING_CHART_ID AND G.PERIOD>'202202' AND G.PERIOD<=T.YEAR_MONTH), " +
                    "               (SELECT SUM(DEBIT+UN_DEBIT) FROM GL_CHART_AUX G WHERE G.COMPANY_ID=T.COMPANY_ID AND G.YEAR=T.YEAR " +
                    "                   AND G.ACC_OPERATING_CHART_ID=T.ACC_OPERATING_CHART_ID AND G.PERIOD>'202202' AND G.PERIOD<=T.YEAR_MONTH)) " +
                    "         ELSE T.DEBIT END DEBIT, " +
                    "       CASE WHEN SUMMARY='本期合计' THEN   " +
                    "           SUM(T.CREDIT) OVER (PARTITION BY C.CODE, T.YEAR_MONTH)   " +
                    "         WHEN SUMMARY='本年累计' THEN   " +
                    "           NVL((SELECT SUM(CREDIT+UN_CREDIT) FROM GL_CHART G WHERE G.COMPANY_ID=T.COMPANY_ID AND G.YEAR=T.YEAR " +
                    "                   AND G.ACC_OPERATING_CHART_ID=T.ACC_OPERATING_CHART_ID AND G.PERIOD>'202202' AND G.PERIOD<=T.YEAR_MONTH), " +
                    "               (SELECT SUM(CREDIT+UN_CREDIT) FROM GL_CHART_AUX G WHERE G.COMPANY_ID=T.COMPANY_ID AND G.YEAR=T.YEAR " +
                    "                   AND G.ACC_OPERATING_CHART_ID=T.ACC_OPERATING_CHART_ID AND G.PERIOD>'202202' AND G.PERIOD<=T.YEAR_MONTH)) " +
                    "         ELSE T.CREDIT END CREDIT,   " +
                    "       SUM(T.YE+T.DEBIT-T.CREDIT) OVER (PARTITION BY CODE, T.YEAR_MONTH ORDER BY T.POST_DATE, TYPE, T.ITEM_ID) BALANCE，   " +
                    "       ABS(SUM(T.YE+T.DEBIT-T.CREDIT) OVER (PARTITION BY CODE, T.YEAR_MONTH ORDER BY T.POST_DATE, TYPE, T.ITEM_ID)) BALANCE_FORMAT   " +
                    "  FROM (SELECT A.YEAR, A.COMPANY_ID, A.POST_DATE, A.VOUCHER_NO, B.ID ITEM_ID, A.YEAR_MONTH, B.ACC_OPERATING_CHART_ID, B.SUMMARY,   " +
                    "                B.DEBIT, B.CREDIT, 0 YE, 0 OPENING_BALANCE, 2 - HANDWORK_FLAG TYPE   " +
                    "           FROM GL_VOUCHER A, GL_VOUCHER_ITEM B   " +
                    "          WHERE A.ID = B.VOUCHER_ID   " +
                    "         UNION ALL   " +
                    "         SELECT A.YEAR, A.COMPANY_ID, TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD') POST_DATE, NULL VOUCHER_NO, '0' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '期初余额' SUMMARY, 0, 0,   " +
                    "                OPENING_BALANCE YE, OPENING_BALANCE, 0 TYPE   " +
                    "           FROM GL_CHART A   " +
                    "         UNION ALL   " +
                    "         SELECT A.YEAR, A.COMPANY_ID, TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD') POST_DATE, NULL VOUCHER_NO, '0' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '期初余额' SUMMARY, 0, 0,   " +
                    "                SUM(OPENING_BALANCE) YE,  0 OPENING_BALANCE, 0 TYPE   " +
                    "           FROM GL_CHART_AUX A   " +
                    "          GROUP BY A.YEAR, A.COMPANY_ID, REPLACE(TO_CHAR(A.PERIOD / 100), '.', '-') || '-01', A.PERIOD, A.ACC_OPERATING_CHART_ID  " +
                    "          UNION ALL   " +
                    "          SELECT A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')) POST_DATE, NULL VOUCHER_NO, 'Z1' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '本期合计' SUMMARY, 0, 0, 0 YE, 0 OPENING_BALANCE, 3 TYPE   " +
                    "           FROM GL_CHART A   " +
                    "         UNION ALL   " +
                    "         SELECT A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')) POST_DATE, NULL VOUCHER_NO, 'Z1' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '本期合计' SUMMARY, 0, 0, 0 YE,  0 OPENING_BALANCE, 3 TYPE   " +
                    "           FROM GL_CHART_AUX A   " +
                    "          GROUP BY A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')), A.PERIOD, A.ACC_OPERATING_CHART_ID   " +
                    "          UNION ALL   " +
                    "          SELECT A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')) POST_DATE, NULL VOUCHER_NO, 'Z2' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '本年累计' SUMMARY, 0, 0, 0 YE, 0 OPENING_BALANCE, 4 TYPE   " +
                    "           FROM GL_CHART A   " +
                    "         UNION ALL   " +
                    "         SELECT A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')) POST_DATE, NULL VOUCHER_NO, 'Z2' ITEM_ID,   " +
                    "                A.PERIOD YEAR_MONTH, A.ACC_OPERATING_CHART_ID, '本年累计' SUMMARY, 0, 0, 0 YE,  0 OPENING_BALANCE, 4 TYPE   " +
                    "           FROM GL_CHART_AUX A   " +
                    "          GROUP BY A.YEAR, A.COMPANY_ID, LAST_DAY(TO_DATE(TO_CHAR(A.PERIOD)||'01','YYYYMMDD')), A.PERIOD, A.ACC_OPERATING_CHART_ID   "  +
                    "          ) T, MAS_ACC_OPERATING_CHART C" +
                    " WHERE T.ACC_OPERATING_CHART_ID = C.ID   " +
                    "   AND T.COMPANY_ID ='" + company + "' AND (T.YEAR BETWEEN '" + year + "' AND '" + tillYear + "') " +
                    "   AND (T.YEAR_MONTH BETWEEN '" + month + "' AND '" + tillMonth + "') " + accCodeCon + auxNameConC +
                    " ORDER BY C.CODE, T.YEAR_MONTH, T.POST_DATE, T.TYPE, T.ITEM_ID");
        }



        //取结果集
        Query query = entityManager.createNativeQuery(sSql.toString());
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        voucherItemsMaps = query.getResultList(); //结果集


        return voucherItemsMaps;
    }

    /**
     * 查询年初余额
     *
     * @param company
     * @param year
     * @param accountCode
     * @param aux
     * @return
     */
    public Map<String, String> findInitBalanc(String company, int year, String accountCode, String aux) {
        Map<String, String> map = new HashMap();
        if (aux == null) {
            map = initBalanceRepository.findAllByCompanyAndYearAndAccountCode(company, accountCode, year);
        } else {
            map = initAuxRepository.findAllByCompanyAndYearAndAccountCode(company, accountCode, year);

        }
        return map;
    }


    /**
     * 自定义获取某年某月的第一天
     *
     * @param year
     * @param month
     * @return
     */
    public static String getFirstDayOfMonth(int year, int month) {
        Calendar calendar = Calendar.getInstance();
        //设置年份
        calendar.set(Calendar.YEAR, year);
        //设置月份
        calendar.set(Calendar.MONTH, month - 1);
        //获取某月最小天数
        int firstDay = calendar.getMinimum(Calendar.DATE);
        //设置日历中月份的最小天数
        calendar.set(Calendar.DAY_OF_MONTH, firstDay);
        //格式化日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        return sdf.format(calendar.getTime());
    }

    /**
     * 自定义获取某年某月的最后一天
     *
     * @param year
     * @param month
     * @return
     */
    public static String getLastDayOfMonth(int year, int month) {
        Calendar calendar = Calendar.getInstance();
        //设置年份
        calendar.set(Calendar.YEAR, year);
        //设置月份
        calendar.set(Calendar.MONTH, month - 1);
        //获取某月最大天数
        int lastDay = calendar.getActualMaximum(Calendar.DATE);
        //设置日历中月份的最大天数
        calendar.set(Calendar.DAY_OF_MONTH, lastDay);
        //格式化日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        return sdf.format(calendar.getTime());
    }

    /**
     * 获取时间戳(精确到秒)
     *
     * @param date_str
     * @return
     */
    public static long dateTimeStamp(String date_str) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            return sdf.parse(date_str).getTime() / 1000;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }


}
